package cn.org.rapid_framework.generator.provider.db.table.model.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import cn.org.rapid_framework.generator.GeneratorConstants;
import cn.org.rapid_framework.generator.GeneratorProperties;
import cn.org.rapid_framework.generator.util.DBHelper;
import cn.org.rapid_framework.generator.util.StringHelper;

/**
 * <b>function:</b> 获取表注释名称
 * 
 * @author hoojo
 * @createDate 2017-6-10 下午5:05:03
 * @file TableHelper.java
 * @package cn.org.rapid_framework.generator.provider.db.table.model.util
 * @project RapidFramework
 * @blog http://blog.csdn.net/IBM_hoojo
 * @email hoojo_@126.com
 * @version 1.0
 */
public abstract class TableCommentHelper {

	private static final String MYSQL = "select table_name, table_comment from information_schema.tables t where t.table_schema = DATABASE() and table_name = '%s' ";
	private static final String DB2 = "SELECT tabname, remarks FROM SYSCAT.TABLES WHERE TABSCHEMA IN (SELECT CURRENT SCHEMA FROM SYSIBM.DUAL) AND UPPER(TABNAME) = UPPER('%s') ";
	private static final String DM = "SELECT table_name, comments FROM (SELECT A.TABLE_NAME AS TABLE_NAME,DECODE(B.COMMENT$,NULL, A.TABLE_NAME,B.COMMENT$) AS COMMENTS FROM \"SYS\".\"USER_TABLES\" A LEFT JOIN \"SYS\".\"SYSTABLECOMMENTS\" B ON  A.TABLE_NAME=B.TVNAME) WHERE  TABLE_NAME ='%s'";
	private static final String H2 = "SELECT a.table_name, a.remarks FROM information_schema.columns a JOIN INFORMATION_SCHEMA.CONSTRAINTS B ON a.table_name = b.table_name WHERE  A.TABLE_SCHEMA=SCHEMA() AND B.CONSTRAINT_TYPE='PRIMARY KEY' AND UPPER(A.TABLE_NAME) = UPPER('%s') ";
	private static final String ORACLE = "SELECT table_name, comments FROM user_tab_comments WHERE table_type='TABLE' AND table_name ='%s'";
	private static final String SQLSERVER = "select a.name, cast(b.value as varchar) comment from sys.tables a, sys.extended_properties b where a.type='U' and a.object_id=b.major_id and b.minor_id=0 and a.name='%s'";

	private static final Map<String, String> COMMENT_SQL = new HashMap<String, String>();

	static {
		COMMENT_SQL.put("mysql", MYSQL);
		COMMENT_SQL.put("db2", DB2);
		COMMENT_SQL.put("dm", DM);
		COMMENT_SQL.put("h2", H2);
		COMMENT_SQL.put("oracle", ORACLE);
		COMMENT_SQL.put("sqlserver", SQLSERVER);
		COMMENT_SQL.put("sqlserver2005", SQLSERVER);
	}

	public static String getComment(Connection conn, String tableName) {

		String sql = COMMENT_SQL.get(GeneratorProperties.getDatabaseType(GeneratorConstants.DATABASE_TYPE.code));
		if (StringHelper.isNotBlank(sql)) {
			return queryForString(conn, sql, tableName);
		}
		return null;
	}

	private static String queryForString(Connection conn, String sql, String tableName) {
		Statement s = null;
		ResultSet rs = null;
		try {
			sql = String.format(sql, new Object[] { tableName });
			
			s = conn.createStatement();
			rs = s.executeQuery(sql);
			if (rs.next()) { return rs.getString(2); }
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBHelper.close(null, s, rs);
		}
	}
}
